  1 <?php
  2 class Dbout
  3 {
  4 var $conn;
  5 function Dbout($host="localhost",$user="root",$pass="",$db="mytest")
  6 {
  7   if(!$this->conn=mysql_connect($host,$user,$pass))
  8   die("can't connect to mysql sever");
  9   mysql_select_db($db,$this->conn);
 10   mysql_query("SET NAMES 'UTF-8'");
 11 }
 12 function execute($sql)
 13 {
 14    return mysql_query($sql,$this->conn);
 15 }
 16 function findCount($sql)
 17 {
 18     $result=$this->execute($sql);
 19     return mysql_num_rows($result);
 20 }
 21 function findBySql($sql)
 22 {
 23     $array=array();
 24     $result=mysql_query($sql);
 25     $i=0;
 26     while($row=mysql_fetch_assoc($result))
 27        {
 28       $array[$i]=$row; 
 29    $i++;
 30        }
 31     return $array;
 32 }
 33 //$con的几种情况
 34 //空：返回全部记录
 35 //array：eg. array('id'=>'1') 返回id=1的记录
 36 //string :eg. 'id=1' 返回id=1的记录
 37 function toExtJson($table,$start="0",$limit="10",$cons="")
 38 {
 39    $sql=$this->generateSql($table,$cons);
 40    $totalNum=$this->findCount($sql);
 41    $result=$this->findBySql($sql." LIMIT ".$start." ,".$limit);
 42    $resultNum = count($result);//当前结果数
 43   $str="";
 44   $str.= "{";
 45   $str.= "'totalCount':'$totalNum',";
 46   $str.="'rows':";
 47   $str.="[";
 48   for($i=0;$i<$resultNum;$i++){
 49    $str.="{"; 
 50    $count=count($result[$i]);
 51    $j=1;
 52    foreach($result[$i] as $key=>$val)
 53    {
 54    if($j<$count)
 55    {
 56    $str.="'".$key."':'".$val."',";
 57    }
 58    elseif($j==$count)
 59    {
 60    $str.="'".$key."':'".$val."'";
 61    }
 62    $j++;
 63             }
 64    
 65    $str.="}";
 66    if ($i != $resultNum-1) {
 67              $str.= ",";
 68          }
 69   }
 70   $str.="]";
 71   $str.="}";
 72   return $str;  
 73 }
 74 function generateSql($table,$cons)
 75 {
 76     $sql="";//sql条件
 77    $sql="select * from ".$table;
 78    if($cons!="")
 79    {
 80    if(is_array($cons))
 81    {
 82      $k=0;
 83      foreach($cons as $key=>$val)
 84   {
 85   if($k==0)
 86   {
 87   $sql.="where '";
 88   $sql.=$key;
 89   $sql.="'='";
 90   $sql.=$val."'";
 91   }else
 92   {
 93   $sql.="and '";
 94   $sql.=$key;
 95   $sql.="'='";
 96   $sql.=$val."'";
 97   }
 98   $k++;
 99   }
100    }else
101    {
102    $sql.=" where ".$cons;
103    }
104    }
105    return $sql;
106 }
107 function toExtXml($table,$start="0",$limit="10",$cons="")
108 {
109    $sql=$this->generateSql($table,$cons);
110    $totalNum=$this->findCount($sql);
111    $result=$this->findBySql($sql." LIMIT ".$start." ,".$limit);
112    $resultNum = count($result);//当前结果数
113       header("Content-Type: text/xml");
114    $xml="<?xml version=\"1.0\"  encoding=\"utf-8\" ?>\n";
115    $xml.="<xml>\n";
116    $xml.="\t<totalCount>".$totalNum."</totalCount>\n";
117    $xml.="\t<items>\n";
118    for($i=0;$i<$resultNum;$i++){
119    $xml.="\t\t<item>\n";
120    foreach($result[$i] as $key=>$val)
121    $xml.="\t\t\t<".$key.">".$val."</".$key.">\n";
122    $xml.="\t\t</item>\n";
123    }
124     $xml.="\t</items>\n";
125     $xml.="</xml>\n";
126     return $xml;
127 }
128 //输出word表格
129 function toWord($table,$mapping,$fileName)
130 {
131    header('Content-type: application/doc'); 
132       header('Content-Disposition: attachment; filename="'.$fileName.'.doc"'); 
133       echo '<html xmlns:o="urn:schemas-microsoft-com:office:office" 
134        xmlns:w="urn:schemas-microsoft-com:office:word" 
135        xmlns="[url=http://www.w3.org/TR/REC-html40]http://www.w3.org/TR/REC-html40[/url]">
136     <head>
137        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8" />
138     <title>'.$fileName.'</title>
139     </head>
140     <body>'; 
141     echo'<table border=1><tr>';
142     if(is_array($mapping))
143     {
144       foreach($mapping as $key=>$val)
145    echo'<td>'.$val.'</td>';
146     }
147     echo'</tr>';
148     $results=$this->findBySql('select * from '.$table);
149     foreach($results as $result)
150     {
151       echo'<tr>';
152       foreach($result as $key=>$val)
153    echo'<td>'.$val.'</td>';
154    echo'</tr>';
155     }
156     echo'</table>';
157     echo'</body>';
158     echo'</html>';
159 }
160 function toExcel($table,$mapping,$fileName)
161 {
162   header("Content-type:application/vnd.ms-excel");
163      header("Content-Disposition:filename=".$fileName.".xls");
164   echo'<html xmlns:o="urn:schemas-microsoft-com:office:office"
165        xmlns:x="urn:schemas-microsoft-com:office:excel"
166        xmlns="[url=http://www.w3.org/TR/REC-html40]http://www.w3.org/TR/REC-html40[/url]">
167        <head>
168        <meta http-equiv="expires" content="Mon, 06 Jan 1999 00:00:01 GMT">
169        <meta http-equiv=Content-Type content="text/html; charset=iso-8859-1">
170        <!--[if gte mso 9]><xml>
171        <x:ExcelWorkbook>
172        <x:ExcelWorksheets>
173                <x:ExcelWorksheet>
174                <x:Name></x:Name>
175                <x:WorksheetOptions>
176                        <x:DisplayGridlines/>
177                </x:WorksheetOptions>
178                </x:ExcelWorksheet>
179        </x:ExcelWorksheets>
180        </x:ExcelWorkbook>
181        </xml><![endif]-->
182        </head>
183     <body link=blue vlink=purple leftmargin=0 topmargin=0>'; 
184     echo'<table width="100%" border="0" cellspacing="0" cellpadding="0">';
185        echo'<tr>';
186     if(is_array($mapping))
187     {
188       foreach($mapping as $key=>$val)
189    echo'<td>'.$val.'</td>';
190     }
191     echo'</tr>';
192     $results=$this->findBySql('select * from '.$table);
193     foreach($results as $result)
194     {
195       echo'<tr>';
196       foreach($result as $key=>$val)
197    echo'<td>'.$val.'</td>';
198    echo'</tr>';
199     }
200     echo'</table>';
201     echo'</body>';
202     echo'</html>';
203 }
204 function Backup($table)
205 {
206   if(is_array ($table))
207   {
208    $str="";
209    foreach($table as $tab)
210    $str.=$this->get_table_content($tab);
211    return $str;
212   }else{
213    return $this->get_table_content($table);
214   }
215 }
216 function Backuptofile($table,$file)
217 {
218   header("Content-disposition: filename=$file.sql");//所保存的文件名
219   header("Content-type: application/octetstream");
220   header("Pragma: no-cache");
221   header("Expires: 0");
222   if(is_array ($table))
223   {
224    $str="";
225    foreach($table as $tab)
226    $str.=$this->get_table_content($tab);
227    echo $str;
228   }else{
229    echo $this->get_table_content($table);
230   }
231 }
232 function Restore($table,$file="",$content="")
233 {
234   //排除file，content都为空或者都不为空的情况
235   if(($file==""&&$content=="")||($file!=""&&$content!=""))
236   echo"参数错误";
237   $this->truncate($table);
238   if($file!="")
239   {
240    if($this->RestoreFromFile($file))
241    return true;
242    else
243    return false;
244   }
245   if($content!="")
246   {
247    if($this->RestoreFromContent($content))
248    return true;
249    else
250    return false;
251   }
252 }
253 //清空表，以便恢复数据
254 function truncate($table)
255 {
256   if(is_array ($table))
257   {
258    $str="";
259    foreach($table as $tab)
260    $this->execute("TRUNCATE TABLE $tab");
261   }else{
262    $this->execute("TRUNCATE TABLE $table");
263   }
264 }
265 function get_table_content($table)
266 {
267   $results=$this->findBySql("select * from $table");
268   $temp = "";
269   $crlf="\r\n";
270   foreach($results as $result)
271   {
272    
273    /*(";
274   foreach($result as $key=>$val)
275   {
276    $schema_insert .= " `".$key."`,";
277   }
278   $schema_insert = ereg_replace(",$", "", $schema_insert);
279   $schema_insert .= ") 
280   */
281   $schema_insert = "INSERT INTO  $table VALUES (";
282   foreach($result as $key=>$val)
283   {
284    if($val != "")
285    $schema_insert .= " '".addslashes($val)."',";
286    else
287    $schema_insert .= "NULL,";
288   }
289   $schema_insert = ereg_replace(",$", "", $schema_insert);
290   $schema_insert .= ");$crlf";
291   $temp = $temp.$schema_insert ;
292   }
293   return $temp;
294 }
295 function RestoreFromFile($file){
296   if (false !== ($fp = fopen($file, 'r'))) {
297    $sql_queries = trim(fread($fp, filesize($file)));
298    $this->splitMySqlFile($pieces, $sql_queries);
299    foreach ($pieces as $query) {
300     if(!$this->execute(trim($query)))
301     return false;
302    }
303    return true;
304   }
305   return false;
306 }
307 function RestoreFromContent($content)
308 {
309   $content = trim($content);
310   $this->splitMySqlFile($pieces, $content);
311   foreach ($pieces as $query) {
312    if(!$this->execute(trim($query)))
313    return false;
314   }
315   return true;
316 }
317 function splitMySqlFile(&$ret, $sql)
318 {
319   $sql= trim($sql);
320   $sql=split(';',$sql);
321   $arr=array();
322   foreach($sql as $sq)
323   {
324     if($sq!="");
325     $arr[]=$sq;
326   }
327   $ret=$arr;
328   return true;
329 }
330 }
331
335 ?>